﻿using Corina.Datamodel.Entities;
using Corina.Enumerations;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Corina.DataContext.Service
{
    public class NationalStandardConfigService : ServiceBase
    {
        public DataTable GetAllItems(string connectionString, string name, string code, int specimenKindId, int index, int size, ref int count)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";
            StringBuilder builder = new StringBuilder();

            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY tns.Code DESC) AS ROW_NUMBER, tnsc.*, tns.Name AS GBName, tns.Code, tns.DocumentName, tns.[Description],tsk.Title as SpecimenKind");
            builder.Append("  FROM t_national_standard_config AS tnsc ");
            builder.Append("LEFT JOIN t_national_standard AS tns ON tns.Id = tnsc.DocumentId ");
            builder.Append("LEFT JOIN CORINA_PROD.dbo.t_specimen_kind AS tsk ON tsk.Id = tnsc.SpecimenKindId ");
            builder.Append("WHERE 1=1 ");

            #region Conditions
            builder.Append("AND tns.Code LIKE @Code ");
            paras.Add(new SqlParameter("@Code", string.Format("%{0}%", code)));

            builder.Append("AND tnsc.Name LIKE @Name ");
            paras.Add(new SqlParameter("@Name", string.Format("%{0}%", name)));

            if (specimenKindId > 0)
            {
                builder.Append("AND tsk.Id = @SpecimenKind ");
                paras.Add(new SqlParameter("@SpecimenKind", specimenKindId));
            }
            #endregion

            builder.Append(") AS query ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}", cstring, builder), paras.ToArray());

            builder.Append("WHERE query.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY query.[ROW_NUMBER]");

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}", qstring, builder), paras.ToArray());
            return table;
        }

        public ResultValue Insert(string connectionString, NationalStandardConfigInfo entity)
        {
            if (Exist(connectionString, entity.SpecimenKindId, entity.Name))
            {
                return ResultValue.Exists;
            }
            bool flag= DbOperator.UpdateEntity(connectionString, entity, EntityOperation.Insert);
            return flag ? ResultValue.Succeed : ResultValue.Failed;
        }

        public bool Update(string connectionString, int id, int documentId,string previewImage)
        {
            List<string> properties = new List<string>();
            List<object> values = new List<object>();

            if (documentId > 0)
            {
                properties.Add("DocumentId");
                values.Add(documentId);
            }

            if (!string.IsNullOrEmpty(previewImage))
            {
                properties.Add("PreviewImage");
                values.Add(previewImage);
            }

            return DbOperator.UpdateEntityById<NationalStandardConfigInfo>(connectionString, id, properties, values);
        }

        public bool Remove(string connectionString, int id)
        {
            return DbOperator.RemoveEntityById<NationalStandardConfigInfo>(connectionString, id);
        }

        public bool Exist(string connectionString, int specimenKindId, string name)
        {
            return DbOperator.Exists<NationalStandardConfigInfo>(connectionString, new List<string>() { "SpecimenKindId", "Name" }, new List<object>() { specimenKindId, name });
        }
    }
}
